﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassLibrary.Entity;
using ClassLibrary.Data;
using System.Data;

namespace ClassLibrary.DAO
{
    public class TblCustomersDAO
    {
        private DBService _db = null;

        public TblCustomersDAO(string ConnectionString)
        {
            _db = new DBService(ConnectionString);
        }

        public DataTable InsUpdCustomers(TblCustomers customer, int mode)
        {
            DataTable flag = null;
            try
            {
                flag = _db.ExeSP_DataTable("sp_TblCustomer_InsUpd",
                                           new string[]
                                               {
                                                   "@CustomerIndex", "@CustomerTitle", "@CustomerFirstname",
                                                   "@CustomerLastname", "@CustomerFullname", "@Companyname",
                                                   "@postalnumber", "@postalstreet", "@postalsuburb", "@postalpostcode",
                                                   "@postalstate", "@phonework", "@phonehome",
                                                   "@phonemobile", "@phonefacsimile", "@emailaddress",
                                                   "@CustomerUsername", "@emailpassword", "@PreferContactMethod",
                                                   "@DateCreated", "@IsAdmin", "@mode"
                                               },
                                           new object[]
                                               {
                                                   customer.CustomerIndex, customer.CustomerTitle,
                                                   customer.CustomerFirstname, customer.CustomerLastname,
                                                   customer.CustomerFullname, customer.Companyname,
                                                   customer.Postalnumber, customer.Postalstreet, customer.Postalsuburb,
                                                   customer.Postalpostcode, customer.Postalstate, customer.Phonework,
                                                   customer.Phonehome,
                                                   customer.Phonemobile, customer.Phonefacsimile, customer.Emailaddress,
                                                   customer.CustomerUsername, customer.Emailpassword,
                                                   customer.PreferContactMethod, customer.DateCreated, customer.IsAdmin,
                                                   mode
                                               },
                                           CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public DataTable GetState()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from AUState", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetAdmin(string email, string pass)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("TblCustomer_AdminCheck",
                                         new string[] { "@Email", "@Password" },
                                         new object[] { email, pass }, CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        public bool CheckPostCode(string state, string postcode)
        {
            bool flag = false;
            try
            {
                DataTable dt = _db.ExeSP_DataTable("select * from " +
                                                   "(select b.*  from  AUSuburb b, AUState where AUState.state=b.state and b.state= @state)A  where postcode= @postcode",
                                                   new string[] { "@state", "@postcode" },
                                                   new object[] { state, postcode },
                                                   CommandType.Text);
                if (dt.Rows.Count > 0)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public DataTable GetSuburb(string state, string postcode)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from " +
                                         "(select b.*  from  AUSuburb b, AUState where AUState.state=b.state and b.state= @state)A  where postcode= @postcode",
                                         new string[] { "@state", "@postcode" },
                                         new object[] { state, postcode },
                                         CommandType.Text);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetState_BySuburb(string suburb, string postcode)
        {
            DataTable dt = null;
            try
            {
                dt =
                    _db.ExeSP_DataTable(
                        "select AUState.state, statename from AUSuburb, AUState  where postcode = @postcode and suburb =@suburb and AUState.state = AUSuburb.state",
                        new string[] { "@suburb", "@postcode" },
                        new object[] { suburb, postcode },
                        CommandType.Text);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetSuburb_All()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from AUSuburb order by suburb ASC", false, CommandType.Text);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        // Get postcode
        public DataTable GetPostcode_ByState(string suburb)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select postcode, suburb, state from AUSuburb where Suburb=@suburb",
                                         new string[] { "@suburb" },
                                         new object[] { suburb }, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }


        public TblCustomers GetCustomer(string email, string pass)
        {
            TblCustomers customer = new TblCustomers();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("CheckCustomer",
                    new string[] { "@email", "@pass" },
                    new object[] { email, pass }, CommandType.StoredProcedure);
                foreach (DataRow dr in dt.Rows)
                {
                    customer.CustomerIndex = Convert.ToInt32(dr["CustomerIndex"]);
                    customer.CustomerFirstname = dr["CustomerFirstname"].ToString();
                    customer.CustomerLastname = dr["CustomerLastname"].ToString();
                    // customer.DateCreated = Convert.ToDateTime(dr["DateCreated"].ToString());
                    customer.Emailaddress = dr["Emailaddress"].ToString();
                    //customer.CustomerIndex = Convert.ToInt32(dr["CustomerIndex"].ToString());
                    customer.Phonemobile = dr["Phonemobile"].ToString();
                    customer.CustomerFullname = dr["CustomerFullname"].ToString();
                    customer.Emailpassword = pass;
                    customer.IsAdmin = Convert.ToBoolean(dr["IsAdmin"]);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return customer;
        }

        public DataTable GetCustomerList(string companyName, string FirsName, string LastName, string mobilephone, string Email)
        {

            try
            {
                return _db.ExeSP_DataTable("sp_CustomerList",
                    new string[] { "@CompanyName", "@FirstName", "@LastName", "@Phone", "@Email" },
                    new object[] { companyName, FirsName, LastName, mobilephone, Email },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public DataTable GetCustomerId(int CustomerId)
        {
            try
            {
                return _db.ExeSP_DataTable("sp_GetCustomer",
                    new string[] { "@CustomerID" },
                    new object[] { CustomerId }, CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable GetStaffList(int IsAdmin)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from TblCustomers where IsAdmin= @IsAdmin ",
                    new string[] { "@IsAdmin" },
                    new object[] { IsAdmin }, CommandType.Text);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public TblCustomers CustomerById(int CustomerID)
        {
            TblCustomers customer = new TblCustomers();
            try
            {
                DataTable customerdt = _db.ExeSP_DataTable("sp_GetCustomer",
                    new string[] { "@CustomerID" },
                    new object[] { CustomerID },
                    CommandType.StoredProcedure);
                foreach (DataRow dr in customerdt.Rows)
                {
                    customer.CustomerIndex = Convert.ToInt32(dr["CustomerIndex"].ToString());
                    customer.Companyname = dr["Companyname"].ToString();
                    customer.Emailaddress = dr["emailaddress"].ToString();
                    customer.CustomerTitle = dr["CustomerTitle"].ToString();
                    customer.CustomerFirstname = dr["CustomerFirstname"].ToString();
                    customer.CustomerLastname = dr["CustomerLastname"].ToString();
                    customer.CustomerFullname = dr["CustomerFullname"].ToString();
                    customer.Phonemobile = dr["phonemobile"].ToString();
                    customer.Postalnumber = dr["postalnumber"].ToString();
                    // txtPass.Enabled = false;
                    customer.Postalpostcode = dr["postalpostcode"].ToString();
                    customer.Postalstreet = dr["postalstreet"].ToString();
                    // txtUsername.Text = customerdt["CustomerUsername"].ToString();
                    customer.Phonework = dr["phonework"].ToString();
                    customer.Phonefacsimile = dr["phonefacsimile"].ToString();
                    customer.Phonehome = dr["phonehome"].ToString();
                    customer.Postalsuburb = dr["Postalsuburb"].ToString();
                    customer.Postalstate = dr["Postalstate"].ToString();

                    customer.PreferContactMethod = Convert.ToInt32(dr["PreferContactMethod"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return customer;
        }

        public bool CheckEmail(string Email, int Mode)
        {
            bool flag = false;
            try
            {
                DataTable dt = _db.ExeSP_DataTable("sp_CheckEmail",
                    new string[] { "@Email", "@Mode" },
                    new object[] { Email, Mode }, CommandType.StoredProcedure);

                if (dt.Rows.Count > 0)
                    flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        // Delete customer

        public bool DeleteStaff(TblCustomers customer)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("sp_DeleteStaff",
                    new string[] { "@ID" },
                    new object[] { customer.CustomerIndex },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }


        // select customer moi them vao
        public DataTable getCustomer_New()
        {
            DataTable dt = _db.ExeSP_DataTable("select top 1 * from TblCustomers order by CustomerIndex desc", false,
                                               CommandType.Text);
            return  dt;
        }

    }
}
